package com.qianfeng.project.dao.impl;


//
//                            _ooOoo_  
//                           o8888888o  
//                           88" . "88  
//                           (| -_- |)  
//                            O\ = /O  
//                        ____/`---'\____  
//                      .   ' \\| |// `.  
//                       / \\||| : |||// \  
//                     / _||||| -:- |||||- \  
//                       | | \\\ - /// | |  
//                     | \_| ''\---/'' | |  
//                      \ .-\__ `-` ___/-. /  
//                   ___`. .' /--.--\ `. . __  
//                ."" '< `.___\_<|>_/___.' >'"".  
//               | | : `- \`.;`\ _ /`;.`/ - ` : | |  
//                 \ \ `-. \_ __\ /__ _/ .-` / /  
//         ======`-.____`-.___\_____/___.-`____.-'======  
//                            `=---='  
//  
//         .............................................  
//                  佛祖镇楼            BUG辟易  
//          佛曰:  
//                  写字楼里写字间，写字间里程序员；  
//                  程序人员写程序，又拿程序换酒钱。  
//                  酒醒只在网上坐，酒醉还来网下眠；  
//                  酒醉酒醒日复日，网上网下年复年。  
//                  但愿老死电脑间，不愿鞠躬老板前；  
//                  奔驰宝马贵者趣，公交自行程序员。  
//                  别人笑我忒疯癫，我笑自己命太贱；  


import com.alibaba.druid.util.StringUtils;
import com.qianfeng.project.dao.StudentDao;
import com.qianfeng.project.pojo.Student;
import com.qianfeng.project.utils.DBUtils;
import com.qianfeng.project.utils.SqlUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by jackiechan on 2021/9/2 14:43
 *
 * @author jackiechan
 * @version 1.0
 * @since 1.0
 */

public class StudentDaoImpl implements StudentDao {

    private QueryRunner queryRunner = new QueryRunner(DBUtils.getSource());
    @Override
    public void addStudent(Student student) throws SQLException {
       // String sql = "insert into student (stuName, stuGender, stuPhone, bornDate, stuEmail, stuAddress, gradeId) values (?,?,?,?,?,?,?)";
        String sql = SqlUtils.getSql("student.add");
        long result = queryRunner.insert(sql, new ScalarHandler<>(),student.getStuName(),student.getStuGender(),student.getStuPhone(),student.getBornDate(),student.getStuEmail(),student.getStuAddress(),student.getGradeId());
    }

    @Override
    public List<Student> findStudentsByPage(int pyl, int pageSize) throws SQLException {
        String sql = SqlUtils.getSql("student.querylist");
        List<Student> studentList = queryRunner.query(sql, new BeanListHandler<>(Student.class), pyl, pageSize);
        return studentList;
    }

    @Override
    public long getCount() throws SQLException {
        String sql = SqlUtils.getSql("student.count");
        long count  = queryRunner.query(sql, new ScalarHandler<>());
        return count;
    }

    @Override
    public void deleteById(long stuId) throws SQLException {
        String sql = SqlUtils.getSql("student.delete");
        queryRunner.update(sql,stuId);
    }

    @Override
    public Student findStuById(long id) {
        String sql = SqlUtils.getSql("student.queryid");
        try {
            Student student = queryRunner.query(sql, new BeanHandler<>(Student.class), id);
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void updateStuById(Student student) {
        //sql 需要动态生成,只有传递了参数的列才拼接
        //update xxx set xxx= xx xxxx=xxx,xxxx=xxx
        StringBuffer sqlBuffer = new StringBuffer();

        List params = new ArrayList();//保存参数的集合

        if (!StringUtils.isEmpty(student.getStuName())) {
            sqlBuffer.append("stuName=?,");
            params.add(student.getStuName());//只有需要更新的列才会拼接具体值,先保存起来,后面挨个放
        }
        if (!StringUtils.isEmpty(student.getStuGender())) {
            sqlBuffer.append("stuGender=?,");
            params.add(student.getStuGender());
        }
        if (!StringUtils.isEmpty(student.getStuEmail())) {
            sqlBuffer.append("stuEmail=?,");
            params.add(student.getStuEmail());
        }
        if (!StringUtils.isEmpty(student.getStuPhone())) {
            sqlBuffer.append("stuPhone=?,");
            params.add(student.getStuPhone());
        }
        if (student.getBornDate()!=null) {
            sqlBuffer.append("bornDate=?,");
            params.add(student.getBornDate());
        }
        if (student.getGradeId()>0) {
            sqlBuffer.append("gradeId=?,");
            params.add(student.getGradeId());
        }
        if (!StringUtils.isEmpty(student.getStuAddress())) {
            sqlBuffer.append("stuAddress=?,");
            params.add(student.getStuAddress());
        }

        String setsql = sqlBuffer.toString();
        if (!StringUtils.isEmpty(setsql)) {//代表有 sql 语句,说明至少传递了一个参数
            setsql = setsql.substring(0, setsql.lastIndexOf(","));
            setsql = "UPDATE student set " + setsql +" where stuId =?";//拼接的 sql
            params.add(student.getStuId());//最后拼接 id
            //UPDATE student set stuName=?,stuGender=?,stuEmail=?,bornDate=?,gradeId=? where stuId=?
            System.err.println("拼接的 sql===>"+setsql);
            //经过我们的缜密的分析,sql 语句我们写出来了,我们为什么按照上面的方式写 sql,因为传递的数据不确定
            //现在 sql 写出来,后面还有个参数的传递,就是因为参数的不确定,才导致了我们需要动态拼接 sql,现在怎么传参数
            try {
                queryRunner.update(setsql,params.toArray());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //如果上面的 if 不成立就代表什么都不需要做

    }
}
